package com.efast.dmom.mgmt.service.excel.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.efast.cafe.framework.bean.ResultBean;
import com.efast.cafe.framework.jdbc.JdbcDao;
import com.efast.cafe.framework.jdbc.bean.DBColumn;
import com.efast.cafe.framework.util.SpringWebUtils;
import com.efast.dmom.mgmt.bean.excel.ExcelRuleDataBean;
import com.efast.dmom.mgmt.dao.excel.ExcelUploadRuleConfigurationDao;
import com.efast.dmom.mgmt.dao.excel.UploadExcelFileDao;
import com.efast.dmom.mgmt.service.excel.UploadExcelFileService;
import com.efast.dmom.mgmt.utils.Assist;
import com.efast.dmom.mgmt.utils.ExcelUploadUtil;

import jodd.util.StringUtil;

@Service
public class UploadExcelFileServiceImpl implements UploadExcelFileService {

	@Autowired
	private UploadExcelFileDao uploadExcelFileDao;

	@Autowired
	private ExcelUploadRuleConfigurationDao excelUploadRuleConfigurationDao;

	@Autowired
	@Qualifier("jdbcDao")
	private JdbcDao jdbcDao;

	@Override
	public void uploadByQueryField(File file, String tableName, List<ExcelRuleDataBean> ruleData, Integer rowNumber) {
		try {
			// MultipartFile类型文件转换为file类型
//			File file = new File(mulfile.getOriginalFilename());
//			File file = new File("D:\\Documents\\Downloads\\text.xls");
			// 文件流
			FileInputStream in = new FileInputStream(file);
			try {
				Workbook workbook = ExcelUploadUtil.getWorkbok(in, file);
				// Sheet的数量
				// 设置当前excel中sheet的下标：0开始
				int sheetCount = workbook.getNumberOfSheets();
				for (int i = 0; i < sheetCount; i++) {
					// 遍历第一个Sheet
					Sheet sheet = workbook.getSheetAt(i);
					// 定义一个存储map的集合
					Map<String, Object> map = new HashMap<String, Object>();
					// 获取表名
//				String tableName=ruleData.getTableName();
					// 获取查重字段
					List<ExcelRuleDataBean> ruleDataBean = new ArrayList<ExcelRuleDataBean>();
					for (ExcelRuleDataBean bean : ruleData) {
						if (bean.getRepeatField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRepeatField(bean.getRepeatField());
							ruleDataBean.add(dataBean);
						}
					}
					// 查询插入表的字段名
					List<String> list = new ArrayList<String>();
					List<DBColumn> dbColum = jdbcDao.getColumns(tableName);
					if (dbColum != null && dbColum.size() > 0) {
						for (DBColumn data : dbColum) {
							list.add(data.getColumnName());
						}
					}
					// 为跳过几行数据设置count
					int count = 0;
					for (Row row : sheet) {
						// 默认时跳过第一行插入数据
						if (rowNumber == 1) {
							if (count < 1) {
								count++;
								continue;
							}
						}
						// 若从第二行起插入数据
						if (rowNumber > 1) {
							int number = rowNumber - 1;
							if (count < number) {
								count++;
								continue;
							}
						}
						if (isAllRowEmpty(row,sheet.getRow(0))) {
							continue;
						}
						// 遍历上传的字段数据
						for (ExcelRuleDataBean uploadRuleData : ruleData) {
							Cell cell = row.getCell(uploadRuleData.getExcelNumber() - 1);
							if (cell == null) {
								// System.out.print("null" + "\t");
								continue;
							}
							// 获取对应列号的值
							Object obj = ExcelUploadUtil.getValue(cell);
							if ("".equals(obj)) {
								map.put(uploadRuleData.getFieldName(), null);
							} else {
								map.put(uploadRuleData.getFieldName(), obj);
							}
						}
						Assist assit = new Assist();
						Integer qty;
						if (ruleDataBean != null && ruleDataBean.size() > 0) {
							for (ExcelRuleDataBean data : ruleDataBean) {
								if (ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1)) == null
										|| ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))
												.equals("")) {
									assit.setRequires(Assist.isEq(data.getFieldName()));
									continue;
								}
								assit.setRequires(Assist.andEq(data.getFieldName(),
										ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))));
							}
							qty = uploadExcelFileDao.verifyRepetitionField(tableName, assit);
						} else {
							qty = 0;
						}
						if (qty > 0) {
							if (list != null && list.size() > 0) {
								for (String fieldName : list) {
									if ("last_update_time".equals(fieldName)) {
										map.put("last_update_user", new Date());
									}
									if ("last_update_user".equals(fieldName)) {
										map.put("last_update_user", SpringWebUtils.getRequestUser());
									}
								}
							}
							uploadExcelFileDao.updateEachTable(tableName, map, assit);
						} else {
							if (list != null && list.size() > 0) {
								for (String fieldName : list) {
									if ("create_time".equals(fieldName)) {
										map.put("create_time", new Date());
									}
									if ("create_user".equals(fieldName)) {
										map.put("create_user", SpringWebUtils.getRequestUser());
									}
									if ("company_code".equals(fieldName)) {
										map.put("company_code", SpringWebUtils.getRequestCompany());
									}
								}
							}
							uploadExcelFileDao.insertEachTable(tableName, map);
						}
						map.clear();
					}
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void uploadByQueryRule(File file, String tableName, Integer rowNumber) {
		try {
			// MultipartFile类型文件转换为file类型
//			File file = new File(mulfile.getOriginalFilename());
			// 文件流
			FileInputStream in = new FileInputStream(file);
			Workbook workbook;
			try {
				workbook = ExcelUploadUtil.getWorkbok(in, file);
				// Sheet的数量
				// 设置当前excel中sheet的下标：0开始
				int sheetCount = workbook.getNumberOfSheets();
				for (int i = 0; i < sheetCount; i++) {
					// 遍历第一个Sheet
					Sheet sheet = workbook.getSheetAt(i);
					// 获取总行数
//				int row=sheet.getLastRowNum();
					// 定义一个存储map的集合
					Map<String, Object> map = new HashMap<String, Object>();
					// 查询规则表
					List<ExcelRuleDataBean> list = excelUploadRuleConfigurationDao
							.selectExcelRuleByTableName(tableName);
					// 获取规则表中配置了外键关联字段的
					List<ExcelRuleDataBean> ruleDataBeanRelationFields = new ArrayList<ExcelRuleDataBean>();
					// 获取查重字段
					List<ExcelRuleDataBean> ruleDataBean = new ArrayList<ExcelRuleDataBean>();
					for (ExcelRuleDataBean bean : list) {
						if (bean.getRepeatField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRepeatField(bean.getRepeatField());
							ruleDataBean.add(dataBean);
						} else if (bean.getRelationField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRelationField(bean.getRelationField());
							ruleDataBeanRelationFields.add(dataBean);
						}
					}
					// 查询插入表的字段名
					List<String> list1 = new ArrayList<String>();
					List<DBColumn> dbColum = jdbcDao.getColumns(tableName);
					if (dbColum != null && dbColum.size() > 0) {
						for (DBColumn data : dbColum) {
							list1.add(data.getColumnName());
						}
					}
					// 为跳过几行数据设置count
					int count = 0;
					for (Row row : sheet) {
						// 默认时跳过第一行插入数据
						if (rowNumber == 1) {
							if (count < 1) {
								count++;
								continue;
							}
						}
						// 若从第二行起插入数据
						if (rowNumber > 1) {
							int number = rowNumber - 1;
							if (count < number) {
								count++;
								continue;
							}
						}
						if (isAllRowEmpty(row,sheet.getRow(0))) {
							continue;
						}
						// 遍历上传的字段数据
						for (ExcelRuleDataBean uploadRuleData : list) {
							Cell cell = row.getCell(uploadRuleData.getExcelNumber() - 1);
							if (cell == null) {
								// System.out.print("null" + "\t");
								continue;
							}
							// 获取对应列号的值
							Object obj = ExcelUploadUtil.getValue(cell);
							if ("".equals(obj)) {
								map.put(uploadRuleData.getFieldName(), null);
							} else {
								map.put(uploadRuleData.getFieldName(), obj);
							}
						}
						Assist assit = new Assist();
						Integer qty;
						if (ruleDataBean != null && ruleDataBean.size() > 0) {
							for (ExcelRuleDataBean data : ruleDataBean) {
								if (ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1)) == null
										|| ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))
												.equals("")) {
									assit.setRequires(Assist.isEq(data.getFieldName()));
									continue;
								}
								assit.setRequires(Assist.andEq(data.getFieldName(),
										ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))));
							}
							qty = uploadExcelFileDao.verifyRepetitionField(tableName, assit);
						} else {
							qty = 0;
						}
						if (qty > 0) {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("last_update_time".equals(fieldName)) {
										map.put("last_update_user", new Date());
									}
									if ("last_update_user".equals(fieldName)) {
										map.put("last_update_user", SpringWebUtils.getRequestUser());
									}
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
								}
							}
							uploadExcelFileDao.updateEachTable(tableName, map, assit);
						} else {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("create_time".equals(fieldName)) {
										map.put("create_time", new Date());
									}
									if ("create_user".equals(fieldName)) {
										map.put("create_user", SpringWebUtils.getRequestUser());
									}
									if ("company_code".equals(fieldName)) {
										map.put("company_code", SpringWebUtils.getRequestCompany());
									}
									// 遍历所有配置有外键关联字段的数据
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
								}
							}
							uploadExcelFileDao.insertEachTable(tableName, map);
						}
						map.clear();
					}
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	@Override
	public ResultBean uploadExcelFile(File file, String tableName) {
		ResultBean resultBean = new ResultBean(true);
		try {
			// MultipartFile类型文件转换为file类型
//			File file = new File(mulfile.getOriginalFilename());
			// 文件流
			FileInputStream in = new FileInputStream(file);
			Workbook workbook;
			try {
				workbook = ExcelUploadUtil.getWorkbok(in, file);
				// Sheet的数量
//				int sheetCount = workbook.getNumberOfSheets();
				// 设置当前excel中sheet的下标：0开始
//				for (int i = 0; i < sheetCount; i++) {
					// 遍历第一个Sheet
					Sheet sheet = workbook.getSheetAt(0);
					// 获取总行数
//				int row=sheet.getLastRowNum();
					// 定义一个存储map的集合
					Map<String, Object> map = new HashMap<String, Object>();
					// 查询规则表
					List<ExcelRuleDataBean> list = excelUploadRuleConfigurationDao
							.selectExcelRuleByTableName(tableName);
					if (list.size() == 0) {
						resultBean.setSuccess(false);
						resultBean.setErrorCode("规则表中字段定义异常，请检查规则表定义！");
						return resultBean;
					}
					// 获取规则表中配置了外键关联字段的
					List<ExcelRuleDataBean> ruleDataBeanRelationFields = new ArrayList<ExcelRuleDataBean>();
					// 获取查重字段
					List<ExcelRuleDataBean> ruleDataBean = new ArrayList<ExcelRuleDataBean>();
					for (ExcelRuleDataBean bean : list) {
						if (bean.getRepeatField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRepeatField(bean.getRepeatField());
							ruleDataBean.add(dataBean);
						} else if (bean.getRelationField() != null) {
							if (!bean.getRelationField().matches(".*-.*")) {
								resultBean.setSuccess(false);
								resultBean.setErrorCode("excel规则配置中字段"+bean.getFieldName()+"配置有误");
								return resultBean;
							}
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRelationField(bean.getRelationField());
							ruleDataBeanRelationFields.add(dataBean);
						}
					}
					// 查询插入表的字段名
					List<String> list1 = new ArrayList<String>();
					List<DBColumn> dbColum = jdbcDao.getColumns(tableName);
					if (dbColum != null && dbColum.size() > 0) {
						for (DBColumn data : dbColum) {
							list1.add(data.getColumnName());
						}
					}
					// 为跳过几行数据设置count
					int count = 0;
					for (Row row : sheet) {
						// 默认时跳过第一行插入数据
						if (count < 1) {
							count++;
							continue;
						}
						if (isAllRowEmpty(row,sheet.getRow(0))) {
							continue;
						}
						// 遍历上传的字段数据
						for (ExcelRuleDataBean uploadRuleData : list) {
							Cell cell = row.getCell(uploadRuleData.getExcelNumber() - 1);
							if (cell == null) {
								// System.out.print("null" + "\t");
								continue;
							}
							// 获取对应列号的值
							Object obj = ExcelUploadUtil.getValue(cell);
							if ("".equals(obj)) {
								map.put(uploadRuleData.getFieldName(), null);
							} else {
								map.put(uploadRuleData.getFieldName(), obj);
							}
						}
						Assist assit = new Assist();
						Integer qty;
						if (ruleDataBean != null && ruleDataBean.size() > 0) {
							for (ExcelRuleDataBean data : ruleDataBean) {
								if (ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1)) == null
										|| ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))
												.equals("")) {
									assit.setRequires(Assist.isEq(data.getFieldName()));
									continue;
								}
								assit.setRequires(Assist.andEq(data.getFieldName(),
										ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))));
							}
							qty = uploadExcelFileDao.verifyRepetitionField(tableName, assit);
						} else {
							qty = 0;
						}
						if (qty > 0) {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("last_update_time".equals(fieldName)) {
										map.put("last_update_user", new Date());
									}
									if ("last_update_user".equals(fieldName)) {
										map.put("last_update_user", SpringWebUtils.getRequestUser());
									}
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
								}
							}
							uploadExcelFileDao.updateEachTable(tableName, map, assit);
						} else {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("create_time".equals(fieldName)) {
										map.put("create_time", new Date());
									}
									if ("create_user".equals(fieldName)) {
										map.put("create_user", SpringWebUtils.getRequestUser());
									}
									if ("company_code".equals(fieldName)) {
										map.put("company_code", SpringWebUtils.getRequestCompany());
									}
									// 遍历所有配置有外键关联字段的数据
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
									
								}
							}
							uploadExcelFileDao.insertEachTable(tableName, map);
						}
						map.clear();
					}
//				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return resultBean;
	}

	@Override
	public void partUploadExcelFile(File file, String tableName) {
		try {
			// MultipartFile类型文件转换为file类型
//			File file = new File(mulfile.getOriginalFilename());
			// 文件流
			FileInputStream in = new FileInputStream(file);
			Workbook workbook;
			try {
				workbook = ExcelUploadUtil.getWorkbok(in, file);
				// Sheet的数量
				int sheetCount = workbook.getNumberOfSheets();
				// 设置当前excel中sheet的下标：0开始
				for (int i = 0; i < sheetCount; i++) {
					// 遍历第一个Sheet
					Sheet sheet = workbook.getSheetAt(i);
					// 获取总行数
//				int row=sheet.getLastRowNum();
					// 定义一个存储map的集合
					Map<String, Object> map = new HashMap<String, Object>();
					// 查询规则表
					List<ExcelRuleDataBean> list = excelUploadRuleConfigurationDao
							.selectExcelRuleByTableName(tableName);
					// 获取规则表中配置了外键关联字段的
					List<ExcelRuleDataBean> ruleDataBeanRelationFields = new ArrayList<ExcelRuleDataBean>();
					// 获取查重字段
					List<ExcelRuleDataBean> ruleDataBean = new ArrayList<ExcelRuleDataBean>();
					for (ExcelRuleDataBean bean : list) {
						if (bean.getRepeatField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRepeatField(bean.getRepeatField());
							ruleDataBean.add(dataBean);
						} else if (bean.getRelationField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRelationField(bean.getRelationField());
							ruleDataBeanRelationFields.add(dataBean);
						}
					}
					// 查询插入表的字段名
					List<String> list1 = new ArrayList<String>();
					List<DBColumn> dbColum = jdbcDao.getColumns(tableName);
					if (dbColum != null && dbColum.size() > 0) {
						for (DBColumn data : dbColum) {
							list1.add(data.getColumnName());
						}
					}
					// 为跳过几行数据设置count
					int count = 0;
					for (Row row : sheet) {
						// 默认时跳过第一行插入数据
						if (count < 1) {
							count++;
							continue;
						}
						if (isAllRowEmpty(row,sheet.getRow(0))) {
							continue;
						}
						// 遍历上传的字段数据
						for (ExcelRuleDataBean uploadRuleData : list) {
							Cell cell = row.getCell(uploadRuleData.getExcelNumber() - 1);
							if (cell == null) {
								// System.out.print("null" + "\t");
								continue;
							}
							// 获取对应列号的值
							Object obj = ExcelUploadUtil.getValue(cell);
							if ("".equals(obj)) {
								map.put(uploadRuleData.getFieldName(), null);
							} else {
								map.put(uploadRuleData.getFieldName(), obj);
							}
						}
						Assist assit = new Assist();
						Integer qty;
						if (ruleDataBean != null && ruleDataBean.size() > 0) {
							for (ExcelRuleDataBean data : ruleDataBean) {
								if (ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1)) == null
										|| ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))
												.equals("")) {
									assit.setRequires(Assist.isEq(data.getFieldName()));
									continue;
								}
								assit.setRequires(Assist.andEq(data.getFieldName(),
										ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))));
							}
							qty = uploadExcelFileDao.verifyRepetitionField(tableName, assit);
						} else {
							qty = 0;
						}
						if (qty > 0) {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("last_update_time".equals(fieldName)) {
										map.put("last_update_user", new Date());
									}
									if ("last_update_user".equals(fieldName)) {
										map.put("last_update_user", SpringWebUtils.getRequestUser());
									}
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
								}
							}
							uploadExcelFileDao.updateEachTable(tableName, map, assit);
						} else {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("create_time".equals(fieldName)) {
										map.put("create_time", new Date());
									}
									if ("create_user".equals(fieldName)) {
										map.put("create_user", SpringWebUtils.getRequestUser());
									}
									if ("company_code".equals(fieldName)) {
										map.put("company_code", SpringWebUtils.getRequestCompany());
									}
									// 遍历所有配置有外键关联字段的数据
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
								}
							}
							map.put("part_no_version", map.get("part_no")+"-"+map.get("version"));
							uploadExcelFileDao.insertEachTable(tableName, map);
						}
						map.clear();
					}
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public void bomItemUploadExcelFile(File file, String tableName) {
		try {
			// MultipartFile类型文件转换为file类型
//			File file = new File(mulfile.getOriginalFilename());
			// 文件流
			FileInputStream in = new FileInputStream(file);
			Workbook workbook;
			try {
				workbook = ExcelUploadUtil.getWorkbok(in, file);
				// Sheet的数量
				int sheetCount = workbook.getNumberOfSheets();
				// 设置当前excel中sheet的下标：0开始
				for (int i = 0; i < sheetCount; i++) {
					// 遍历第一个Sheet
					Sheet sheet = workbook.getSheetAt(i);
					// 获取总行数
//				int row=sheet.getLastRowNum();
					// 定义一个存储map的集合
					Map<String, Object> map = new HashMap<String, Object>();
					// 查询规则表
					List<ExcelRuleDataBean> list = excelUploadRuleConfigurationDao
							.selectExcelRuleByTableName(tableName);
					// 获取规则表中配置了外键关联字段的
					List<ExcelRuleDataBean> ruleDataBeanRelationFields = new ArrayList<ExcelRuleDataBean>();
					// 获取查重字段
					List<ExcelRuleDataBean> ruleDataBean = new ArrayList<ExcelRuleDataBean>();
					for (ExcelRuleDataBean bean : list) {
						if (bean.getRepeatField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRepeatField(bean.getRepeatField());
							ruleDataBean.add(dataBean);
						} else if (bean.getRelationField() != null) {
							ExcelRuleDataBean dataBean = new ExcelRuleDataBean();
							dataBean.setFieldName(bean.getFieldName());
							dataBean.setFieldComment(bean.getFieldComment());
							dataBean.setExcelNumber(bean.getExcelNumber());
							dataBean.setRelationField(bean.getRelationField());
							ruleDataBeanRelationFields.add(dataBean);
						}
					}
					// 查询插入表的字段名
					List<String> list1 = new ArrayList<String>();
					List<DBColumn> dbColum = jdbcDao.getColumns(tableName);
					if (dbColum != null && dbColum.size() > 0) {
						for (DBColumn data : dbColum) {
							list1.add(data.getColumnName());
						}
					}
					// 为跳过几行数据设置count
					int count = 0;
					for (Row row : sheet) {
						// 默认时跳过第一行插入数据
						if (count < 1) {
							count++;
							continue;
						}
						if (isAllRowEmpty(row,sheet.getRow(0))) {
							continue;
						}
						// 遍历上传的字段数据
						for (ExcelRuleDataBean uploadRuleData : list) {
							Cell cell = row.getCell(uploadRuleData.getExcelNumber() - 1);
							if (cell == null) {
								// System.out.print("null" + "\t");
								continue;
							}
							// 获取对应列号的值
							Object obj = ExcelUploadUtil.getValue(cell);
							if ("".equals(obj)) {
								map.put(uploadRuleData.getFieldName(), null);
							} else {
								map.put(uploadRuleData.getFieldName(), obj);
							}
						}
						Assist assit = new Assist();
						Integer qty;
						if (ruleDataBean != null && ruleDataBean.size() > 0) {
							for (ExcelRuleDataBean data : ruleDataBean) {
								if (ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1)) == null
										|| ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))
												.equals("")) {
									assit.setRequires(Assist.isEq(data.getFieldName()));
									continue;
								}
								assit.setRequires(Assist.andEq(data.getFieldName(),
										ExcelUploadUtil.getValue(row.getCell(data.getExcelNumber() - 1))));
							}
							qty = uploadExcelFileDao.verifyRepetitionField(tableName, assit);
						} else {
							qty = 0;
						}
						if (qty > 0) {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("last_update_time".equals(fieldName)) {
										map.put("last_update_user", new Date());
									}
									if ("last_update_user".equals(fieldName)) {
										map.put("last_update_user", SpringWebUtils.getRequestUser());
									}
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
								}
							}
							uploadExcelFileDao.updateEachTable(tableName, map, assit);
						} else {
							if (list1 != null && list1.size() > 0) {
								for (String fieldName : list1) {
									if ("create_time".equals(fieldName)) {
										map.put("create_time", new Date());
									}
									if ("create_user".equals(fieldName)) {
										map.put("create_user", SpringWebUtils.getRequestUser());
									}
									if ("company_code".equals(fieldName)) {
										map.put("company_code", SpringWebUtils.getRequestCompany());
									}
									// 遍历所有配置有外键关联字段的数据
									for ( ExcelRuleDataBean bean: ruleDataBeanRelationFields) {
										if (bean.getFieldName().equals(fieldName)) {
											// 截取外键关联字段，获取对应表名，对应表与当前表关联字段，对应表将要获取插入的字段值，当前表需要插入的字段
											String[] split = bean.getRelationField().split("-");
											if (split.length == 4) {
												Map<String,Object> oprationData = new HashMap<String,Object>();
												String otherTableName = split[0];
												String otherTableRelationField = split[1];
												String oterTableRelationInsertField = split[2];
												String currentTableInsertFiled = split[3];
												oprationData.put("otherTableName", otherTableName);
												oprationData.put("otherTableRelationFieldValue", map.get(fieldName));
												oprationData.put("otherTableRelationField", otherTableRelationField);
												oprationData.put("oterTableRelationInsertField", oterTableRelationInsertField);
												// 通过·配置的数据，查询需要插入当前表字段的数据
												List<Object> selectdataByOtherTableRelationField = uploadExcelFileDao.selectdataByOtherTableRelationField(oprationData);
												// 将需要插入的数据set进集合中，等待插入
												map.put(currentTableInsertFiled, selectdataByOtherTableRelationField.get(0));
											}
										}
									}
								}
							}
							map.replace("part_no_version", map.get("part_no_version"), map.get("part_no_version") + "-V1");
							uploadExcelFileDao.insertEachTable(tableName, map);
						}
						map.clear();
					}
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		
	}
	public  boolean isAllRowEmpty(Row row,Row firstRow) {
		int count = 0;
		//单元格数量
		int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
		//判断多少个单元格为空
		for (int c = 0; c < rowCount; c++) {
			Cell cell = row.getCell(c);
			if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || StringUtil.isEmpty((cell+"").trim())){
				count += 1;
			}
		}
		if (count == rowCount) {
			return true;
		}
 
		return  false;
	}
}
